Questions

9 of 15

1What are the main categories of data types available in MySQL?

2What is the difference between CHAR and VARCHAR data types?

3Which data type would you use to store dates and times in MySQL?

4What is the difference between INT, FLOAT, and DECIMAL data types?

5What is the use of the TEXT and BLOB data types, and how are they different from VARCHAR?

6How does MySQL handle precision and scale in DECIMAL(M, D) columns internally, and how do these differ from FLOAT and DOUBLE in terms of storage and accuracy?

7When storing time zone–aware data, what are the differences in behavior and use cases between DATETIME, TIMESTAMP, and CONVERT_TZ() in MySQL?

8If you define a VARCHAR(255) column with utf8mb4 encoding, how does MySQL calculate the maximum possible storage size for that column, and how does it differ from CHAR(255)?

9What are the advantages and limitations of using ENUM and SET data types in terms of performance, flexibility, and schema evolution?

10Explain how MySQL internally stores and sorts values of type BLOB and TEXT. What happens when you try to index a TEXT column?

11How do signed and unsigned integer types affect query results, index usage, and storage size? Can you demonstrate an example where overflow behavior differs?

12In what scenarios would using a JSON column be preferable to a normalized table structure, and what are the internal storage and indexing implications of JSON in MySQL 8.0?

13How does MySQL’s BIT(M) type differ from BOOLEAN, TINYINT(1), and binary string types (BINARY, VARBINARY) in terms of storage, representation, and retrieval?

14If you define a composite index on multiple columns of different data types (e.g., INT, VARCHAR, and DATE), how do the internal data type differences influence sorting, comparisons, and index efficiency?

15What are the practical implications of using CHAR vs VARCHAR for columns in InnoDB tables with varying row lengths and frequent updates? How does this choice affect row fragmentation and performance?

mysql

Functions and Operators

09 / 15

What are the advantages and limitations of using ENUM and SET data types in terms of performance, flexibility, and schema evolution?

Advantages and Limitations of ENUM and SET Data Types

ENUM and SET are string-based data types that store predefined values efficiently, but they differ in flexibility and schema evolution impact.

Advantages of ENUM

1
Efficient storage — stored internally as integers (1 byte or 2 bytes).
2
Faster comparisons than regular VARCHAR since MySQL compares integer indexes.
3
Ensures data integrity by restricting values to predefined options.
4
Good for fields with mutually exclusive options (e.g., status, gender, priority).

Limitations of ENUM

1
Changing ENUM values (adding/removing/reordering) requires an ALTER TABLE, which is expensive.
2
Reordering ENUM values affects stored index mapping and may break application logic.
3
Not ideal for frequently changing option lists.
4
Hard to extend when values grow large; poor for dynamic metadata-driven systems.

Advantages of SET

1
Allows storing multiple selected values in a single column.
2
Stored efficiently as bitmaps (1 to 8 bytes depending on number of options).
3
Fast for membership checks using bitwise operations.
4
Good for tags, features, or permissions where multiple selections are common.

Limitations of SET

1
Adding or removing values requires ALTER TABLE.
2
Limited to a maximum of 64 distinct options.
3
Querying becomes harder because values are stored as bitwise combinations.
4
Not ideal for normalized database design — violates 1NF when storing lists.

Key Differences and Use Cases

1
ENUM is for single-choice fields; SET is for multi-choice fields.
2
ENUM and SET are both efficient but inflexible when schema changes are frequent.
3
ENUM is best for small, stable lists; SET is best for compact multi-value attributes.
4
For dynamic and fast-changing values, a lookup table is more flexible than ENUM/SET.